class MapController < ApplicationController
  @@offsetx = 690
  @@offsety = 190
  @@planning = "cells_basic"
  def getcellstore
   
    cell =	User.find_by_sql("select count(cell_id) from cells20110506 where cell_id like upper('%#{params['query']}%') or cellname like '%#{params['query']}%';")
    size = cell[0]["count"];
    cell =	User.find_by_sql("select * from cells20110506 where cell_id like upper('%#{params['query']}%') or cellname like '%#{params['query']}%' order by cell_id limit #{params['limit']} offset #{params['start']};")
    
    txt = "{results:#{size},rows:["
    for k in 0..cell.size-1
      txt = txt + cell[k].to_json + ','
    end
    txt = txt[0..-2] + "]}"
    render :text => txt
  end

  def get_id
    text = _panto('cell_id', params['cell_id'])
    render :text => text
  end

  def _panto(field, value)
    cells =  User.find_by_sql("select x(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lon, y(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lat from cells20110506 where #{field}='#{value}';")
    return text = cells[0].to_json
  end

  def grid_detail

    cells_900 = User.find_by_sql("select * from field_top10 where grid_id = #{params['grid_id']} and type = 'GSM900'")
    cells_1800 = User.find_by_sql("select * from field_top10 where grid_id = #{params['grid_id']} and type = 'GSM1800'")
    cell_acount=10

    txt = "{ results: #{cell_acount}, rows: ["
    txt = txt +"{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_0+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_0+"\""+","+"\"cellname900\":\""+cells_900[0].cellname0+"\""+","+"\"addr900\":\""+cells_900[0].addr0+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_0+"\""+",""\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_0+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname0+"\""+",\"addr1800\":\""+cells_1800[0].addr0+"\""+",\"d900\":\""+cells_900[0].d0+"\""+",\"d1800\":\""+cells_1800[0].d0+"\""+"}"
    txt = txt+"," +"{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_1+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_1+"\""+","+"\"cellname900\":\""+cells_900[0].cellname1+"\""+","+"\"addr900\":\""+cells_900[0].addr1+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_1+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_1+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname1+"\""+",\"addr1800\":\""+cells_1800[0].addr1+"\""+",\"d900\":\""+cells_900[0].d1+"\""+",\"d1800\":\""+cells_1800[0].d1+"\""+"}"
    txt = txt+"," + "{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_2+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_2+"\""+","+"\"cellname900\":\""+cells_900[0].cellname2+"\""+","+"\"addr900\":\""+cells_900[0].addr2+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_2+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_2+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname2+"\""+",\"addr1800\":\""+cells_1800[0].addr2+"\""+",\"d900\":\""+cells_900[0].d2+"\""+",\"d1800\":\""+cells_1800[0].d2+"\""+"}"
    txt = txt +","+"{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_3+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_3+"\""+","+"\"cellname900\":\""+cells_900[0].cellname3+"\""+","+"\"addr900\":\""+cells_900[0].addr3+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_3+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_3+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname3+"\""+",\"addr1800\":\""+cells_1800[0].addr3+"\""+",\"d900\":\""+cells_900[0].d3+"\""+",\"d1800\":\""+cells_1800[0].d3+"\""+"}"
    txt = txt +"," + "{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_4+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_4+"\""+","+"\"cellname900\":\""+cells_900[0].cellname4+"\""+","+"\"addr900\":\""+cells_900[0].addr4+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_4+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_4+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname4+"\""+",\"addr1800\":\""+cells_1800[0].addr4+"\""+",\"d900\":\""+cells_900[0].d4+"\""+",\"d1800\":\""+cells_1800[0].d4+"\""+"}"
    txt = txt+"," + "{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_5+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_5+"\""+","+"\"cellname900\":\""+cells_900[0].cellname5+"\""+","+"\"addr900\":\""+cells_900[0].addr5+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_5+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_5+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname5+"\""+",\"addr1800\":\""+cells_1800[0].addr5+"\""+",\"d900\":\""+cells_900[0].d5+"\""+",\"d1800\":\""+cells_1800[0].d5+"\""+"}"
    txt = txt+"," + "{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_6+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_6+"\""+","+"\"cellname900\":\""+cells_900[0].cellname6+"\""+","+"\"addr900\":\""+cells_900[0].addr6+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_6+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_6+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname6+"\""+",\"addr1800\":\""+cells_1800[0].addr6+"\""+",\"d900\":\""+cells_900[0].d6+"\""+",\"d1800\":\""+cells_1800[0].d6+"\""+"}"
    txt = txt+"," + "{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_7+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_7+"\""+","+"\"cellname900\":\""+cells_900[0].cellname7+"\""+","+"\"addr900\":\""+cells_900[0].addr7+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_7+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_7+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname7+"\""+",\"addr1800\":\""+cells_1800[0].addr7+"\""+",\"d900\":\""+cells_900[0].d7+"\""+",\"d1800\":\""+cells_1800[0].d7+"\""+"}"
    txt = txt+","+"{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_8+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_8+"\""+","+"\"cellname900\":\""+cells_900[0].cellname8+"\""+","+"\"addr900\":\""+cells_900[0].addr8+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_8+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_8+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname8+"\""+",\"addr1800\":\""+cells_1800[0].addr8+"\""+",\"d900\":\""+cells_900[0].d8+"\""+",\"d1800\":\""+cells_1800[0].d8+"\""+"}"
    txt = txt+","+ "{\"grid_id\""+":"+"\""+cells_900[0].grid_id+"\""+","+"\"cell900_c\""+":"+"\""+cells_900[0].cell_9+"\""+","+"\"cell900_p\""+":"+"\""+cells_900[0].receivepower_9+"\""+","+"\"cellname900\":\""+cells_900[0].cellname9+"\""+","+"\"addr900\":\""+cells_900[0].addr9+"\","+"\"cell1800_c\""+":"+"\""+cells_1800[0].cell_9+"\""+","+"\"cell1800_p\""+":"+"\""+cells_1800[0].receivepower_9+"\""+","+"\"cellname1800\":\""+cells_1800[0].cellname9+"\""+",\"addr1800\":\""+cells_1800[0].addr9+"\""+",\"d900\":\""+cells_900[0].d9+"\""+",\"d1800\":\""+cells_1800[0].d9+"\""+"}"
    txt = txt+"]}"
    render :text => txt
  end
  def getcells
    if params[:style]!=''
      s=params[:style]
    else
      s=session[:style]
    end
    session[:style]=s
    if params[:style] == "polygen"
      if params[:p]!=''
        p=params[:p]
      else
        p = session[:p]
      end
      #p = p[9..-3]
      session[:p]=p
      txt = "["
      if(s=="polygen")
        rows = User.find_by_sql("select cell_id,bcchno,cellname,direction,x(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lon, y(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lat ,celltype from #{@@planning} where st_within(the_geom,st_translate(ST_GeomFromEWKT('SRID=900913;#{p}'),-#{@@offsetx},-#{@@offsety}))")
      else
        rows = User.find_by_sql("select cell_id,bcchno,cellname,direction,x(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lon, y(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lat ,celltype from #{@@planning} where st_within(the_geom,ST_GeomFromEWKT('SRID=900913;#{p}'))")
      end
      for i in 0..rows.size-1 do
        json = JSON.generate "cell_id"=>rows[i].cell_id, "bcchno"=>rows[i].bcchno, "cellname"=>rows[i].cellname,"direction"=>rows[i].direction,"lon"=>rows[i].lon,"lat"=>rows[i].lat,"celltype"=>rows[i].celltype
        if i != 0
          txt = txt + ','
        end
        txt = txt + json
      end
      txt = txt + "]"
    elsif params[:style] == "region"
      if params[:region_name]!=''
        session[:region_name]=params[:region_name]
      end
      txt = "["
      #region = User.find_by_sql("select the_geom from region where region_name = '#{params[:region_name]}'")
      #rows = User.find_by_sql("select celltype, cell_id, bcchno, cellname, direction,st_translate(st_x(a.the_geom))+690 as lon, st_translate(st_y(a.the_geom))+190 as lat from cells_basic where st_within(the_geom,'#{region[0]["the_geom"]}')")
      rows = User.find_by_sql("select a.celltype,a.cell_id,a.bcchno,a.cellname,a.direction, x(st_translate(a.the_geom,#{@@offsetx},#{@@offsety})) as lon, y(st_translate(a.the_geom,#{@@offsetx},#{@@offsety})) as lat from #{@@planning} a, region b where b.region_name = '#{params[:region_name]}' and st_within (a.the_geom,b.the_geom)")
      for i in 0..rows.size-1 do
        json = JSON.generate "cell_id"=>rows[i].cell_id, "bcchno"=>rows[i].bcchno, "cellname"=>rows[i].cellname,"direction"=>rows[i].direction,"lon"=>rows[i].lon,"lat"=>rows[i].lat,"celltype"=>rows[i].celltype#"lon"=>rows[i].lon.to_f/111319.490793276,"lat"=>rows[i].lat.to_f/114306.663089776
        if i != 0
          txt = txt + ','
        end
        txt = txt + json
      end
      txt = txt + "]"
    end
    render :text=>txt
  end
  def getcell
    cells = User.find_by_sql("select *,st_x(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lon, st_y(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lat from #{@@planning} where cell_id = '#{params[:cell_id]}'")
    ActiveRecord::Base.include_root_in_json = false
    # txt = "{"
    txt = ""
    txt = txt + cells[0].to_json
    render :text => txt
  end
  def getregions_1
    regions=User.find_by_sql("select region from region group by region")
    txt = "["
    for i in 0..regions.size-1 do
      txt = txt + '"'+regions[i]["region"]+'",'
    end
    txt = txt[0..-2]+"]"
    render :text=>txt
  end
  def getregions_2
    regions=User.find_by_sql("select region_name from region where region='#{params[:region1]}' group by region_name")
    txt = "["
    for i in 0..regions.size-1 do
      txt = txt + '"'+regions[i]["region_name"]+'",'
    end
    txt = txt[0..-2]+"]"
    render :text=>txt
  end
  def read
    p=session[:p]
    s=session[:style]
    size = 0
    txt = "["
    if s == "polygen"
      cells = User.find_by_sql("select count(cell_id)  from #{@@planning} where st_within(the_geom,st_translate(ST_GeomFromEWKT('SRID=900913;#{p}'),-#{@@offsetx},-#{@@offsety}))")
      size = cells[0]["count"]
      cells = User.find_by_sql("select *,st_x(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lon, st_y(st_translate(the_geom,#{@@offsetx},#{@@offsety})) as lat from #{@@planning} where st_within(the_geom,st_translate(ST_GeomFromEWKT('SRID=900913;#{p}'),-#{@@offsetx},-#{@@offsety})) order by cell_id,cellname limit #{params[:limit]} offset #{params[:start]}")
    elsif s == "region"
      cells = User.find_by_sql("select count(a.cell_id) from #{@@planning} a, region b where b.region_name = '#{session[:region_name]}' and st_within (a.the_geom,b.the_geom)")
      size = cells[0]["count"]
      cells = User.find_by_sql("select a.*,st_x(st_translate(a.the_geom,#{@@offsetx},#{@@offsety})) as lon, st_y(st_translate(a.the_geom,#{@@offsetx},#{@@offsety})) as lat from #{@@planning} a, region b  where b.region_name = '#{session[:region_name]}' and st_within (a.the_geom,b.the_geom) order by a.cell_id,a.cellname limit #{params[:limit]} offset #{params[:start]}")
      #cells2 = User.find_by_sql("select * from cells20110506 where st_within(the_geom,ST_GeomFromEWKT('SRID=900913;POLYGON((#{result}))'))")
    end
    ActiveRecord::Base.include_root_in_json = false
    txt = "{ results: #{size}, rows: ["
    for k in 0..cells.count-1
      txt = txt + cells[k].to_json+','
    end
    if cells.size == 0
      txt = txt + "]}"
    else
      txt = txt[0..-2] + "]}"
    end
    render :text => txt
  end
  def delete
    cells = User.find_by_sql("select cell_id from #{@@planning} where cell_id = '#{params[:cell_id]}'")
    if cells.size > 0
      User.find_by_sql("delete from #{@@planning} where cell_id = '#{params[:cell_id]}'")
      render :text=>'cell deleted'
    else
      render :text=>"cell can not be found"
    end
  end
  def update
    #User.find_by_sql("update cells20110506 set bcchno=#{params[:bcchno]},bsic='#{params[:bsic]}',tch='#{params[:tch]}',direction=#{params[:direction]},vange=#{params[:vange]},bspwrb=#{params[:bspwrb]},antennagain=#{params[:antennagain]},celltype='#{params[:celltype]}',cell_emluator_model='#{params[:cell_emluator_model]}',cellname='#{params[:cellname]}',addr='#{params[:addr]}',height=#{params[:height]},grid_id=#{params[:grid_id]},type_code=#{params[:type_code]},lon=#{params[:lon]},lat=#{params[:lat]} where cell_id = '#{params[:cell_id]}'")
    cell = User.find_by_sql("select cell_id from #{@@planning} where cell_id = '#{params[:cell_id]}'")
    if cell.size > 0
      User.find_by_sql("update #{@@planning} set cellname = '#{params[:cellname]}' , cgi = '#{params[:cgi]}' ,msc_id = '#{params[:msc_id]}' , bsc_id = '#{params[:bsc_id]}' , bcchno = #{params[:bcchno]} , bsic = '#{params[:bsic]}' , power = #{params[:power]} , antennagain = #{params[:antennagain]} , tch = '#{params[:tch]}', celltype = '#{params[:celltype]}' ,cell_emluator_model = '#{params[:cell_emluator_model]}', type_code = #{params[:type_code]}, address ='#{params[:address]}', lon = #{params[:lon]}, lat = #{params[:lat]}, grid_id = #{params[:grid_id]}, the_geom = st_setsrid(st_makepoint(#{params[:lon]},#{params[:lat]}),900913), e_vange = #{params[:e_vange]}, m_vange = #{params[:m_vange]}, vange = #{params[:vange]}, height = #{params[:height]},direction = #{params[:direction]}where cell_id = '#{params[:cell_id]}'")
    else
      User.find_by_sql("insert into #{@@planning} (cell_id,cellname,cgi,msc_id,bsc_id,bcchno,bsic,power,antennagain,tch,celltype,cell_emluator_model,type_code,address,lon,lat,grid_id,the_geom,e_vange,m_vange,vange,height,direction) values ('#{params[:cell_id]}','#{params[:cellname]}','#{params[:cgi]}','#{params[:msc_id]}','#{params[:bsc_id]}',#{params[:bcchno]},'#{params[:bsic]}',#{params[:power]},#{params[:antennagain]},'#{params[:tch]}','#{params[:celltype]}','#{params[:cell_emluator_model]}',#{params[:type_code]},'#{params[:address]}',#{params[:lon]},#{params[:lat]},#{params[:grid_id]},st_setsrid(st_makepoint(#{params[:lon]},#{params[:lat]}),900913),#{params[:e_vange]},#{params[:m_vange]},#{params[:vange]},#{params[:height]},#{params[:direction]})")
    end
    #User.find_by_sql("update cells20110506 set cellname = 'updated_by_LIU' where cell_id = '#{params[:cell_id]}'")
    render :text=>'Nothing'
  end
  def updatelocation
    User.find_by_sql(" update #{@@planning} set lon = #{params[:lon]}, lat = #{params[:lat]},grid_id = #{params[:grid_id]}, the_geom = st_setsrid(st_makepoint(#{params[:lon]},#{params[:lat]}),900913) where cell_id = '#{params[:cell_id]}' ")
    render :text=>"Success"
  end
  def updatedirection
    User.find_by_sql(" update #{@@planning} set direction = #{params[:direction]} where cell_id = '#{params[:cell_id]}' ")
    render :text => "Success"
  end
  def setcollection
    txt = ""
    if user_signed_in?
      descs = User.find_by_sql("select * from collection where user_email = '#{current_user.email}' and description = '#{params[:description]}'")
      if descs.size > 0
        txt = "描述已存在"
      else
        User.find_by_sql("insert into collection (user_email,the_geom,description) values ('#{current_user.email}','#{session[:p]}','#{params[:description]}')")
        txt = "所选区域已保存"
      end
    else
      txt = "请先登录"
    end
    render :text=>txt
  end
  def getcollections
    txt = ""
    if user_signed_in?
      descs = User.find_by_sql("select  description from collection where user_email = '#{current_user.email}'")
      txt =txt + "["
      for i in 0..descs.size-1 do
        txt = txt + '"'+descs[i]["description"]+'",'
      end
      txt = txt[0..-2]+"]"
      render :text=>txt
    end
  end
  def getgeom
    txt = ""
    if user_signed_in?
      descs = User.find_by_sql("select  the_geom from collection where user_email = '#{current_user.email}' and description = '#{params[:description]}'")
      txt = descs[0]["the_geom"]
    end
    render :text=>txt
  end
end
